<?
function FlushCustomer ($TableName, $tmp) {
	global $DBConnection;

	if ($tmp) $TEMPORARY = "TEMPORARY";
	$DBC = new mydb(DB_USER, DB_PASSWORD, DB_NAME2, DB_HOST);

	$SQL = "SELECT kodecustomer,nama,cr_term,kdtype FROM customer ";
	if (GetParam("kdtype","")) $SQL .= " WHERE kdtype='".GetParam("kdtype","")."'";
	$MyResult = $DBC->dbc->get_results($SQL,ARRAY_N);
 	$session_file = substr(md5(time()), 0, 8);
   	$session_file = "/tmp/".$session_file.".tmp";
	BufferToFile ($MyResult,$session_file);

	$DBConnection = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);   	
	$SQL = "DROP TABLE IF EXISTS `$TableName`;";
	$DBConnection->dbc->query($SQL);

	$SQL= "CREATE $TEMPORARY TABLE `$TableName` (
			  `kodecustomer` varchar(20) NOT NULL default '',
			  `nama` varchar(50) default NULL,
			  `top` int(4) default NULL,
			  `kdtype` varchar(20) default NULL
			  ) TYPE=MyISAM;";
	$DBConnection->dbc->query($SQL);

   	$SQL = "LOAD DATA INFILE '$session_file' INTO TABLE $TableName FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'";
	$DBConnection->dbc->query($SQL);
	unlink($session_file);
}

function SelectTmpMemotrans() {
	global $DBConnection,$DBMysql;
            //$TEMP = "TEMPORARY";
			//$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));
			FlushCustomer ("tblcustomer", "$TEMP");
			list($day,$month,$year) = explode("/",GetParam("transDate",""));
			$current_date = "$year-$month-$day";

			$SQL = "DROP TABLE IF EXISTS `ais_piutang_terpiutang`";
			$DBConnection->QueryNoResult($SQL,$DBMysql);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_piutang_terpiutang` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,	
                                          `nobkta` varchar(16) default NULL,
                                          `skada` varchar(16) default NULL,
                                          `dk` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
					  `rate` decimal(8,2) default NULL,
					  `jumlah` decimal(16,2) default NULL,
					  `cr_term` tinyint(4)default NULL,
					  `aging` varchar(4)default NULL
					)
				   ";
			$DBConnection->QueryNoResult($SQL,$DBMysql);
			//print mysql_error()."$SQL";

			$SQL = "
					INSERT INTO ais_piutang_terpiutang
                                        SELECT ais_piutang.KTRASL, TGLBKT, NOBKT,NOBKTA, SKADA, DK, DEBTUR, DEBNAM, URAIAN, CURR,RATE, JUMLAH,IFNULL(TOP,0) AS TOP, 
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_customer.cr_term DAY ),'0',
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_customer.cr_term DAY ),'30',
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_customer.cr_term DAY ),'60','60g')))
                                        AS aging
                                        FROM ais_piutang
                                          LEFT JOIN tblcustomer ON (ais_piutang.debtur = tblcustomer.kodecustomer)
                                          WHERE srt='1' AND ais_piutang.tglbkt<='$current_date' AND tblcustomer.kodecustomer='".GetParam("debtur","")."'
				   ";
			if (GetParam("office","")) 
				$SQL .= " AND ais_piutang.ktrasl = '".GetParam("office","")."'";
			$SQL .= " order by nobkt";

			$DBConnection->QueryNoResult($SQL,$DBMysql);
            //print $SQL;die();

			$SQL = "DROP TABLE IF EXISTS `ais_piutang_terbayar`";
			$DBConnection->QueryNoResult($SQL,$DBMysql);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_piutang_terbayar` (
					  `nobkt` varchar(16) default NULL,					  
					  `nobkta` varchar(16) default NULL,
                      `bayar` varchar(32) default NULL
					)
				   ";
			$DBConnection->QueryNoResult($SQL,$DBMysql);

			$SQL = "
					INSERT INTO ais_piutang_terbayar
					SELECT ais_piutang.NOBKT, ais_piutang.NOBKTA, SUM(ais_piutang.JUMLAH) AS BAYAR
					FROM ais_piutang
                                        LEFT JOIN tblcustomer ON (ais_piutang.debtur = tblcustomer.kodecustomer)
                                        WHERE srt='2' AND ais_piutang.tglbkt<='$current_date' AND ais_piutang.ktrasl = '".GetParam("office","")."' AND tblcustomer.KDTYPE='".GetParam("kdtype","")."'
                                        group by nobkta
                                        order by nobkta
				   ";
                        //print $SQL;die();
			$DBConnection->QueryNoResult($SQL,$DBMysql);

			$SQL = "DROP TABLE IF EXISTS `ais_piutang_remain`";
			$DBConnection->QueryNoResult($SQL,$DBMysql);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_piutang_remain` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,
                                          `nobkta` varchar(16) default NULL,
                                          `skada` varchar(16) default NULL,
                                          `dk` varchar(16) default NULL,                                          
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
                                          `rate` decimal(8,2) default NULL,
                                          `cr_term` tinyint(4)default NULL,
					  `jumlah` varchar(32)default NULL,
                                          `aging` varchar(5)default NULL
					)
				   ";
			$DBConnection->QueryNoResult($SQL,$DBMysql);

                        $SQL = "
                                        INSERT INTO ais_piutang_remain
                                        SELECT 
                                        ais_piutang_terpiutang.ktrasl,
                                        ais_piutang_terpiutang.tglbkt,
                                        ais_piutang_terpiutang.nobkt,
                                        ais_piutang_terpiutang.nobkta,
                                        ais_piutang_terpiutang.skada,
                                        ais_piutang_terpiutang.dk,
                                        ais_piutang_terpiutang.debtur,
                                        ais_piutang_terpiutang.debnam,
                                        ais_piutang_terpiutang.uraian,
                                        ais_piutang_terpiutang.curr,
                                        ais_piutang_terpiutang.rate,
                                        ifnull(ais_piutang_terpiutang.cr_term, 0) AS top,
                                        ifnull(ais_piutang_terpiutang.jumlah,0)-ifnull( ais_piutang_terbayar.bayar,0) as jumlahcurr,
                                        ais_piutang_terpiutang.aging
                                        FROM
                                        ais_piutang_terpiutang
                                        LEFT OUTER JOIN  ais_piutang_terbayar ON (ais_piutang_terpiutang.nobkta =  ais_piutang_terbayar.nobkta)
                                        WHERE
                                        (ifnull( ais_piutang_terbayar.nobkt, '') = '') OR (ifnull(ais_piutang_terpiutang.jumlah,0)- ifnull(ais_piutang_terbayar.bayar,0)) <> 0
                                        ORDER BY debtur,nobkt
                        ";
                        //print $SQL;
			$DBConnection->QueryNoResult($SQL,$DBMysql);
}
?>